<?php
/*
 * code to display budget report asynchronously in budgetreport.php
*/
//check user has logged in
include("../inc_files/utils/checksession.php");
// Create connection
include("../inc_files/utils/dbconnection.php");
//query for all customers
//if the year has been specified get it otherwise set it to this year
$projectID = $_GET["projectid"];

//query for details of project
$query = ("SELECT ProjectTitle FROM projects WHERE ProjectID = '{$projectID}';");



//query for details of the tasks
$query = ("SELECT tasks.TaskID, Title, Budget FROM tasks WHERE Project = '{$projectID}';");

$result = $mysqli->query( $query );

//count the number of returned results
$resultCount = $result->num_rows;

//if there are some results returned create a table showing the results
if( $resultCount > 0){
	$totalProjectWork = 0;
	$totalProjectBudget = 0;

	echo "<table class=\"tabularreport\">
			<tr>
			<th style=\"width:30%\">Task</th>
			<th style=\"width:50%\" colspan=\"2\">Timesheets</th>
			<th style=\"width:20%\">Budget</th>
			</tr>";

	while($row = mysqli_fetch_array($result))
	{

		//get the timesheet records for the current task
		$queryTimesheets = ("SELECT
				FirstName, LastName, SUM(TotalWork) AS TotalWork, Task
				FROM
				(SELECT TL.TotalWork, staff.FirstName, staff.LastName, timesheets.TimesheetID,
				Task, staff.UserID FROM timesheets INNER JOIN staff ON staff.UserID = timesheets.Timesheet_User
				INNER JOIN (SELECT Task, Timesheet,
				SUM(SunHours + MonHours + TueHours + WedHours + ThurHours + FriHours + SatHours) AS TotalWork
				FROM timesheet_lines
				GROUP BY Task , Timesheet) AS TL ON TL.Timesheet = timesheets.TimesheetID) AS T
				WHERE task = {$row['TaskID']} GROUP BY UserID;");

		$resultTimesheets = $mysqli->query( $queryTimesheets );
		$totalTaskWork = 0;
		//count the number of returned results
		$resultCountTimesheets = $resultTimesheets->num_rows;

		if( $resultCountTimesheets > 0){
			while($rowTs = mysqli_fetch_array($resultTimesheets))
			{
				echo "<tr>";
				echo "<td></td>";
				echo "<td>".$rowTs['FirstName']." ".$rowTs['LastName']."</td>";
				echo "<td>".$rowTs['TotalWork']." hours</td>";
				echo "<td></td>";
				$totalTaskWork += $rowTs['TotalWork'];
				echo "</tr>";
			}
		}
		if($totalTaskWork > (int)$row['Budget']){
			 $class = "subtotalsrow overbudget"; 
		} else {
			 $class = "subtotalsrow";
		}
		
		echo "<tr>";
		echo "<td class=\"subtotalsrow\">" . $row['Title'] . "</td>";
		echo "<td class=\"subtotalsrow\">Total:</td><td class=\"{$class}\">".$totalTaskWork." hours</td>";
		echo "<td class=\"{$class}\">". $row['Budget'] ." hours</td>";
		echo "</tr>";


		$totalProjectWork += $totalTaskWork;
		$totalProjectBudget +=  $row['Budget'];


	}
	if($totalProjectWork > $totalProjectBudget){
		$class = "tabreporttotal overbudget";
	} else {
		$class = "tabreporttotal";
	}
	echo "<tr><td class=\"tabreporttotal\"></td>";
	echo "<td class=\"tabreporttotal\">Project total:</td><td class=\"{$class}\">".$totalProjectWork." hours</td>";
	echo "<td class=\"{$class}\">". $totalProjectBudget ." hours</td>";
	echo "</tr>";


	echo "</table>";
}
//if no results returned say so.
else
{

	echo"No records returned";
}
//tidy up database connection
$mysqli->close();

//function to convert dates from dates entered by the user
//to dates for MySQL
function formatdate($date){
	$datearray = explode('/' , $date);
	$date = $datearray[2].'-'.$datearray[1].'-'.$datearray[0];
	return $date;

}

?>